unit clsBackup_Pedido;

interface

uses Dm, dbtables, biblio, sysutils;

type
  TBackupPedido = class(tobject)
  private
    Bkp_pedido: string;
    Ent_codigo: string;
    Ped_numero: string;
  public
    Codigo: Int64;
    dini, dfim: TDateTime;
    //Pedidoq		:double;
    //Pedidof		:double;
    function Gravar(p_Bkp_pedido, p_Ent_codigo, p_Ped_numero: string): Byte;
    function Recuperar(p_Bkp_pedido, p_Ent_codigo, p_Ped_numero: string): Byte;
    function RecuperarPedido(P_bkp_pedido: string; var p_ped_cod1, P_ped_cod2: string): byte;
    function RecuperarPedido2(P_Ped_NUmero: string): string;
    function Excluir(p_Bkp_pedido, p_Ent_codigo, p_Ped_numero: string): Byte;
    function ExcluirPedido(P_Ped_numero: string): byte;
    procedure ExcluirBackup(p_Ped_numero: string);
    procedure consultarPedido(var P_q: TQuery; p_bkp_pedido: string);
    procedure consultarPorPedido(var P_q: TQuery; p_pedido: string);
    function MaiorCodigo: Int64;
    function MenorCodigo: Int64;
    function MaiorPedido: string;
    procedure Consultar(p_data: TDateTime; tipo: byte; regiao: string; var p_q: TQuery);
    procedure Consultar1(p_data: TDateTime; p_ent_codigo: string; var p_q: TQuery);
    function Busca_MaiorMenor(dt_ini, dt_fim: TDateTime; tp: byte; situacao, xemp: string): string; overload;
    function Busca_MaiorMenor(var dt_ini, dt_fim: TDateTime; tp: integer; situacao, xemp, p: string): string; overload;

  end;

implementation

uses clsMotCancPedido, clsparametros;

{ TBackupPedido }

procedure TBackupPedido.Consultar;
var
  motivo: TMotCancPedido;
  parame: TParametros;
begin
  p_q.close;
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select Backup_Pedido.Bkp_Pedido,Backup_Pedido.Ent_codigo,');
  p_q.sql.add('PedidosVenda.Ped_dataped,PedidosVenda.ped_pedclie,PedidosVenda.ped_pedrepre,');
  p_q.sql.add('PedidosVenda.ped_obs,PedidosVenda.Ped_Situacao, Regiao_Codigo from Backup_Pedido,PedidosVenda, Entidade where ');
  if (tipo = 0) or (tipo = 3) then begin
    if tipo = 3 then
      p_q.sql.add('((PedidosVenda.Ped_dataped>=:k_P_data and PedidosVenda.Canp_Codigo>=:a and PedidosVenda.Canp_Codigo<=:b) and ')
    else
      p_q.sql.add('((PedidosVenda.Ped_dataped>=:k_P_data) and ');
  end else begin
    if tipo = 5 then
      p_q.sql.add('(((PedidosVenda.Ped_dataped>=:k_P_data and (PedidosVenda.Ped_Situacao=''P'' or PedidosVenda.Ped_Situacao=''A'')) and (PED_NUMPREV is null or PED_NUMPREV ='''') and (PED_NUMERO_ORCAM = '' '' or PED_NUMERO_ORCAM is null)) and ')
    else
      if tipo = 4 then
      p_q.sql.add('(((PedidosVenda.Ped_dataped>=:k_P_data and (PedidosVenda.Tipp_Codigo=:Tp)) and (PED_NUMERO_ORCAM = '' '' or PED_NUMERO_ORCAM is null)) and ')
    else
      if tipo = 2 then
      p_q.sql.add('((PedidosVenda.Ped_dataped>=:k_P_data and (PedidosVenda.Ped_Situacao=:k_Assistencia or PedidosVenda.Ped_Situacao=:k_Assistencia2)) and ')
    else
      p_q.sql.add('((PedidosVenda.Ped_dataped>=:k_P_data and PedidosVenda.Ped_Situacao=:k_Assistencia) and ');
  end;
  p_q.sql.add(' (PedidosVenda.Ent_Codigo=Entidade.Ent_Codigo) and ');
  if tipo <> 4 then
    p_q.sql.add(' (PedidosVenda.Tipp_Codigo<>:Tp) and ');
  p_q.sql.add('(backup_pedido.ped_numero=pedidosvenda.ped_numero)) ');
  if regiao <> '' then
    p_q.sql.add(' and Regiao_Codigo = ' + quotedStr(regiao) + ' ');
  p_q.sql.add('group by Backup_Pedido.Bkp_Pedido,Backup_Pedido.Ent_codigo,PedidosVenda.Ped_dataped,');
  p_q.sql.add('PedidosVenda.ped_pedclie,PedidosVenda.ped_pedrepre,PedidosVenda.ped_obs,PedidosVenda.Ped_Situacao, Regiao_Codigo');
  p_q.parambyname('k_P_data').AsDateTime := p_data;
  if tipo = 1 then p_q.parambyname('k_Assistencia').Asstring := 'A';
  if tipo = 2 then begin
    p_q.parambyname('k_Assistencia').Asstring := 'P';
    p_q.parambyname('k_Assistencia2').Asstring := 'X';
  end;
  if tipo = 3 then begin
    motivo := TMotCancPedido.Create;
    p_q.parambyname('a').Asstring := motivo.Primeiro;
    p_q.parambyname('b').Asstring := motivo.Ultimo;
    motivo.free;
  end;
  parame := TParametros.Create;
  parame.Recuperar('1');
  p_q.parambyname('tp').Asstring := parame.Tipp_Orcamento;
  parame.Free;
end;

procedure TBackupPedido.consultarPedido(var P_q: TQuery;
  p_bkp_pedido: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Backup_Pedido where bkp_pedido=:k_Bkp_pedido');
  p_q.parambyname('k_Bkp_pedido').asstring := strzero(p_bkp_pedido, 9);
end;

procedure TBackupPedido.consultarPorPedido(var P_q: TQuery;
  p_pedido: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Backup_Pedido where bkp_pedido=:k_pedido');
  p_q.parambyname('k_pedido').asstring := strzero(p_pedido, 9);
end;

function TBackupPedido.Excluir(p_Bkp_pedido, p_Ent_codigo,
  p_Ped_numero: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM Backup_Pedido WHERE Bkp_Pedido=:k_Bkp_Pedido and Ent_codigo=:k_Ent_Codigo and Ped_numero=:K_Ped_Numero');
  tq.parambyname('k_Bkp_Pedido').asstring := StrZero(p_Bkp_pedido, 9);
  tq.parambyname('k_Ent_Codigo').asstring := StrZero(p_Ent_codigo, 5);
  tq.parambyname('K_Ped_Numero').asstring := StrZero(p_Ped_numero, 9);
  try
    GeraLog('BACKUP_PEDIDO', 'Excluir');
    tq.execSQL;
    Result := 1;
  except
    Result := 0;
  end;
  tq.close;
  tq.Free;
end;

function TBackupPedido.ExcluirPedido(P_Ped_numero: string): byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM Backup_Pedido WHERE Bkp_Pedido=:K_Ped_Numero');
  tq.parambyname('K_Ped_Numero').asstring := StrZero(p_Ped_numero, 9);
  try
    GeraLog('BACKUP_PEDIDO', 'ExcluirPedido');
    tq.execSQL;
    Result := 1;
  except
    Result := 0;
  end;
  tq.close;
  tq.Free;
end;

function TBackupPedido.Gravar(p_Bkp_pedido, p_Ent_codigo,
  p_Ped_numero: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM Backup_Pedido where Bkp_Pedido=:k_Bkp_Pedido and Ent_codigo=:k_Ent_Codigo and Ped_numero=:K_Ped_Numero');
  tq.parambyname('k_Bkp_Pedido').asstring := StrZero(p_Bkp_pedido, 9);
  tq.parambyname('k_Ent_Codigo').asstring := StrZero(p_Ent_codigo, 5);
  tq.parambyname('K_Ped_Numero').asstring := StrZero(p_Ped_numero, 9);
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO Backup_Pedido(BKP_PEDIDO,ENT_CODIGO,PED_NUMERO) VALUES(' +
      ':k_Bkp_Pedido,' +
      ':k_Ent_Codigo,' +
      ':K_Ped_Numero)');
  end else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE Backup_Pedido SET ' +
      'Bkp_Pedido=:k_Bkp_Pedido,' +
      'Ent_codigo=:k_Ent_Codigo,' +
      'Ped_numero=:K_Ped_Numero ' +
      ' where Bkp_Pedido=:k_Bkp_Pedido and Ent_codigo=:k_Ent_Codigo and Ped_numero=:K_Ped_Numero');
  end;
  tq.parambyname('k_Bkp_Pedido').asstring := strzero(p_Bkp_pedido, 9);
  tq.parambyname('k_Ent_Codigo').asstring := StrZero(p_Ent_codigo, 5);
  tq.parambyname('K_Ped_Numero').asstring := strzero(p_Ped_numero, 9);
  tq.close;
  GeraLog('BACKUP_PEDIDO', 'Gravar');
  tq.execsql;
  result := 1;
  tq.free;
end;

function TBackupPedido.MaiorCodigo;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT max(bkp_pedido) as maior FROM Backup_Pedido where substr(bkp_pedido,1,1) <> ''W'' ');
  tq.open;
  if tq.fieldbyname('maior').IsNull then
    codigo := 0
  else
    Codigo := tq.fieldbyname('maior').AsInteger;
  result := Codigo;
  tq.close;
  tq.free;
end;

function TBackupPedido.MenorCodigo: Int64;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT min(bkp_pedido) as menor FROM Backup_Pedido');
  tq.open;
  if tq.fieldbyname('menor').IsNull then
    codigo := 0
  else
    Codigo := tq.fieldbyname('menor').AsInteger;
  result := Codigo;
  tq.close;
  tq.free;
end;

function TBackupPedido.Recuperar(p_Bkp_pedido, p_Ent_codigo,
  p_Ped_numero: string): Byte;
var
  tq: TQuery;

begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM Backup_Pedido where Bkp_Pedido=:k_Bkp_Pedido and Ent_codigo=:k_Ent_Codigo and Ped_numero=:K_Ped_Numero');
  tq.Params[0].asstring := StrZero(p_Bkp_pedido, 9);
  tq.Params[1].asstring := StrZero(p_Ent_codigo, 5);
  tq.Params[2].asstring := StrZero(p_Ped_numero, 9);
  tq.open;
  Bkp_pedido := StrZero(p_Bkp_pedido, 9);
  Ent_codigo := StrZero(p_Ent_codigo, 5);
  Ped_numero := StrZero(p_Ped_numero, 9);
  if tq.eof then begin
    Result := 0;
  end else begin
    Result := 1;
  end;
  tq.Close;
  tq.Free;

end;

function TBackupPedido.RecuperarPedido(P_bkp_pedido: string;
  var p_ped_cod1, P_ped_cod2: string): byte;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from Backup_Pedido where bkp_pedido=:k_bkp_pedido order by Ped_numero');
  tq.parambyname('k_bkp_pedido').asstring := strzero(P_bkp_pedido, 9);
  tq.open;
  p_ped_cod1 := '*';
  P_ped_cod2 := '*';
  if tq.eof then result := 0 else result := 1;
  while not tq.eof do begin
    if p_ped_cod1 = '*' then
      p_ped_cod1 := tq.fieldbyname('ped_numero').AsString
    else
      p_ped_cod2 := tq.fieldbyname('ped_numero').AsString;
    tq.Next;
  end;
  tq.close;
  tq.free;
end;

function TBackupPedido.RecuperarPedido2;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.close;
  tq.sql.add('Select * from Backup_Pedido where Ped_Numero=:k_Ped_numero');
  tq.parambyname('k_Ped_Numero').asstring := strzero(P_Ped_NUmero, 9);
  tq.open;
  result := tq.fieldbyname('Bkp_pedido').AsString;
  tq.close;
  tq.free;
end;

procedure TBackupPedido.ExcluirBackup;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Delete from Backup_Pedido where Ped_Numero=:a');
  tq.params[0].asstring := p_Ped_numero;
  GeraLog('BACKUP_PEDIDO', 'ExcluirBackup');
  tq.execSQL;
  tq.close;
  tq.Free;
end;

procedure TBackupPedido.Consultar1;
begin
  p_q.close;
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select Backup_Pedido.Bkp_Pedido,Backup_Pedido.Ent_codigo,');
  p_q.sql.add('PedidosVenda.Ped_dataped,PedidosVenda.ped_pedclie,PedidosVenda.ped_pedrepre,');
  p_q.sql.add('PedidosVenda.ped_obs,PedidosVenda.Ped_Situacao from Backup_Pedido,PedidosVenda where ');
  p_q.sql.add('((PedidosVenda.Ped_dataped>=:k_P_data) and ');
  p_q.sql.add('(backup_pedido.ped_numero=pedidosvenda.ped_numero and backup_pedido.Ent_Codigo=:k_P_ent)) ');
  p_q.sql.add('group by Backup_Pedido.Bkp_Pedido,Backup_Pedido.Ent_codigo,PedidosVenda.Ped_dataped,');
  p_q.sql.add('PedidosVenda.ped_pedclie,PedidosVenda.ped_pedrepre,PedidosVenda.ped_obs,PedidosVenda.Ped_Situacao');
  p_q.parambyname('k_P_data').AsDateTime := p_data;
  p_q.parambyname('k_P_ent').Asstring := strzero(p_ent_codigo, 5);
end;

function TBackupPedido.Busca_MaiorMenor(dt_ini, dt_fim: TDateTime;
  tp: byte; situacao, xemp: string): string;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  if (dt_ini = 0) and (dt_fim = 0) then begin
    if tp = 0 then begin
      tq.sql.add('SELECT min(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('p.Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ')
    end else begin
      tq.sql.add('SELECT max(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('p.Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
    end;
    if xemp <> '' then
      tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
    if situacao <> '' then
      tq.ParamByName('K_situacao').AsString := situacao;
  end else begin
    if tp = 0 then begin
      tq.sql.add('SELECT min(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
      tq.sql.add(' and (Ped_dataped >=:K_dt_inicial and Ped_dataped <=:K_dt_final) ');
      if xemp <> '' then
        tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
      if situacao <> '' then
        tq.ParamByName('K_situacao').AsString := situacao;
      tq.ParamByName('K_dt_inicial').AsDateTime := dt_ini;
      tq.ParamByName('K_dt_final').AsDateTime := dt_fim;
    end else begin
      tq.sql.add('SELECT max(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
      tq.sql.add(' and (Ped_dataped >=:K_dt_inicial and Ped_dataped <=:K_dt_final)');
      if xemp <> '' then
        tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
      if situacao <> '' then
        tq.ParamByName('K_situacao').AsString := situacao;
      tq.ParamByName('K_dt_inicial').AsDateTime := dt_ini;
      tq.ParamByName('K_dt_final').AsDateTime := dt_fim;
    end;
  end;
  tq.open;
  Result := tq.fieldbyname('num').AsString;

  tq.close;
  tq.free;
end;

function TBackupPedido.MaiorPedido: string;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT max(ped_numero) as maior FROM Backup_Pedido');
  tq.open;
  if tq.fieldbyname('maior').IsNull then
    Result := '000000000'
  else
    Result := StrZero(tq.fieldbyname('maior').AsInteger + 1, 9);
  tq.close;
  tq.free;
end;

function TBackupPedido.Busca_MaiorMenor(var dt_ini, dt_fim: TDateTime;
  tp: integer; situacao, xemp, p: string): string;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  if (dt_ini = 0) and (dt_fim = 0) then begin
    if tp = 0 then begin
      tq.sql.add('SELECT min(bkp_pedido) as num, min(Ped_dataped) as DataInicial FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('p.Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ')
    end else begin
      tq.sql.add('SELECT max(bkp_pedido) as num, max(Ped_dataped) as DataFinal FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('p.Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
    end;
    if xemp <> '' then
      tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
    if situacao <> '' then
      tq.ParamByName('K_situacao').AsString := situacao;
  end else begin
    if tp = 0 then begin
      tq.sql.add('SELECT min(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
      tq.sql.add(' and (Ped_dataped >=:K_dt_inicial and Ped_dataped <=:K_dt_final) ');
      if xemp <> '' then
        tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
      if situacao <> '' then
        tq.ParamByName('K_situacao').AsString := situacao;
      tq.ParamByName('K_dt_inicial').AsDateTime := dt_ini;
      tq.ParamByName('K_dt_final').AsDateTime := dt_fim;
    end else begin
      tq.sql.add('SELECT max(bkp_pedido) as num FROM Backup_Pedido b ,pedidosVenda p where ');
      if situacao <> '' then
        tq.sql.add('Ped_situacao=:K_situacao and ');
      tq.sql.add('p.ped_numero=b.ped_numero ');
      tq.sql.add(' and (Ped_dataped >=:K_dt_inicial and Ped_dataped <=:K_dt_final)');
      if xemp <> '' then
        tq.sql.add(' and p.emp_codigo in (' + xemp + ')');
      if situacao <> '' then
        tq.ParamByName('K_situacao').AsString := situacao;
      tq.ParamByName('K_dt_inicial').AsDateTime := dt_ini;
      tq.ParamByName('K_dt_final').AsDateTime := dt_fim;
    end;
  end;
  tq.open;
  Result := tq.fieldbyname('num').AsString;
  if (dt_ini = 0) and (dt_fim = 0) then begin
    if tp = 0 then
      dt_ini := tq.fieldbyname('DataInicial').AsDateTime
    else
      dt_fim := tq.fieldbyname('DataFinal').AsDateTime;
  end;

  tq.close;
  tq.free;

end;

end.

